package com.stuManSystem.dao;

import com.stuManSystem.base.BaseDao;
import com.stuManSystem.model.Student;
import com.stuManSystem.util.DBUtil;

import java.sql.SQLException;

public class StudentDao extends BaseDao {
    private static StudentDao sd = null;
    public synchronized static StudentDao getInstance() {
        if(sd==null)
            sd = new StudentDao();
        return sd;
    }

    int showNum=15;
    int filedNum=9;

    public String[][] list(int pageNum) {

       String[][]result=new String[showNum][filedNum];
       int beginNum=(pageNum-1)*showNum;
       int i=0;
       String sql="select *from student limit ?,?";
       Integer[] param={beginNum,showNum};

        rs=db.executeQuery(sql,param);

        try {
            while(rs.next()){
                result[i][0]=String.valueOf(rs.getInt("id"));
                result[i][1]=rs.getString("name");
                result[i][2]=rs.getString("sno");
                result[i][3]=rs.getString("sex");
                result[i][4]=rs.getString("department");
                result[i][5]=rs.getString("hometown");
                result[i][6]=rs.getString("mark");
                result[i][7]=rs.getString("email");
                result[i][8]=rs.getString("tel");
                i++;
//                if(i>14){
//                    break;
//                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return result;
    }

    //添加
    public boolean add(Student stu){

        boolean result=false;
        String sql="insert into student(name,sno,sex,department,hometown,mark,email,tel) values(?,?,?,?,?,?,?,?)";
        String[]param={stu.getName(),stu.getSno(),stu.getSex(),stu.getDepartment(),stu.getHomeTown(),stu.getMark(),stu.getEmail(),stu.getTel()};
        if(db.executeUpdate(sql,param)==1){
            result=true;
        }
        return  result;
    }

    //删除
    public boolean delete(String name,String sno){
        boolean result=false;
        String sql="delete from student where name=? and sno=?";
        String []param={name,sno};
        int count=db.executeUpdate(sql,param);
        if (count==1){
            result=true;
        }
        return result;
    }

    //更新信息
    public boolean update(Student stu){

        boolean result=false;
        String sql="update student set name=?,sno=?,sex=?,department=?,hometown=?,mark=?," +
                "email=?,tel=? where name=?";
        Object[]param={stu.getName(),stu.getSno(),stu.getSex(),stu.getDepartment(),stu.getHomeTown(),
                stu.getMark(),stu.getEmail(),stu.getTel(),stu.getName()};

        if(db.executeUpdate(sql,param)==1){
            result=true;
        }
        return  result;
    }

    //查询
    public String[][] queryByName(String name){
        String sql="select *from student where name like ?";
        String []param={"%"+name+"%"};
        rs=db.executeQuery(sql,param);//结果集
        int size=0;
        try {
            while (rs.next()){
                size++;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        String[][] result=new String[size][filedNum];
        int i=0;
        try {
            rs.beforeFirst();//遍历指针移动到第一行之前
            while(rs.next()){
                result[i][0]=String.valueOf(rs.getInt("id"));
                result[i][1]=rs.getString("name");
                result[i][2]=rs.getString("sno");
                result[i][3]=rs.getString("sex");
                result[i][4]=rs.getString("department");
                result[i][5]=rs.getString("hometown");
                result[i][6]=rs.getString("mark");
                result[i][7]=rs.getString("email");
                result[i][8]=rs.getString("tel");
                i++;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

}
